package com.neusoft.elm.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.neusoft.elm.dao.BusinessDao;
import com.neusoft.elm.po.Business;
import com.neusoft.elm.util.DBUtil;

public class BusinessDaoImpl implements BusinessDao{
	
	
	
	private Connection con = null;
	private PreparedStatement pst = null;
	private ResultSet rs = null;
	
	
	@Override
	public List<Business> listBusiness(String businessName,String businessAddress) {
		List<Business> list = new ArrayList<>();
	    StringBuffer sql = new StringBuffer( "select * from business where 1=1 ");
	    if(businessName!=null&&!businessName.equals("")) {
	    	sql.append( " and businessName like '%"+businessName+"%' ");
	    }
	    if(businessAddress!=null&&!businessAddress.equals("")) {
	    	sql.append( " and businessAddress like '%"+businessAddress+"%' ");
	    }
	    	
	    	try {
	    		con = DBUtil.getConnection();
				pst = con.prepareStatement(sql.toString());
		    	rs = pst.executeQuery();
		    	while(rs.next()) {
		    	    Business business = new Business();
		    	    business.setBusinessId(rs.getInt("businessId"));
		    	    business.setPassword(rs.getString("password"));
		    	    business.setBusinessName(rs.getString("businessName"));
		    	    business.setBusinessAddress(rs.getString("businessAddress"));
		    	    business.setBusinessExplain(rs.getString("businessExplain"));
		    	    business.setStarPrice(rs.getDouble("starPrice"));
		    	    business.setDeliveryPrice(rs.getDouble("deliveryPrice"));
		    	    business.setDeliveryDistance(rs.getString("deliveryDistance"));
		    	    list.add(business);
		    	}
	    	}catch (SQLException e) {
				e.printStackTrace();
			}finally {
				DBUtil.close(rs, pst, con);
			}
	    	return list;
	    	}
	

	@Override
	public int[] addBusiness(List<String> businessNames) {
	    int businessId = 0;
	    int size = businessNames.size();
	    int[] result = new int[size];

	    String getMaxIdSql = "SELECT MAX(businessId) FROM business";
	    String insertBusinessSql = "INSERT INTO business (businessId, businessName, password) VALUES (?, ?, '123')";

	    try {
	        con = DBUtil.getConnection();
	        pst = con.prepareStatement(getMaxIdSql);
	        rs = pst.executeQuery();
	        if (rs.next()) {
	            int maxId = rs.getInt(1);
	            businessId = maxId + 1;
	        }

	        if (businessId != 0) {
	            pst = con.prepareStatement(insertBusinessSql);
	            for (int i = 0; i < size; i++) {
	                pst.setInt(1, businessId++);
	                pst.setString(2, businessNames.get(i));
	                pst.addBatch();
	            }
	            result = pst.executeBatch();
	        }
	    } catch (SQLException e) {
	        e.printStackTrace();
	    } finally {
	        DBUtil.close(rs, pst, con);
	    }
	    return result;
	}
	
	
	@Override
	public int removeBusiness(List<Integer> businessIds) {
	    int result = 0;
	    String delFootSql = "DELETE FROM food WHERE businessId=?";
	    String delBusinessSql = "DELETE FROM business WHERE businessId=?";
	    String updateBusinessSql = "UPDATE business SET businessId=businessId-1 WHERE businessId>?";

	    try {
	        con = DBUtil.getConnection();
	        con.setAutoCommit(false);

	        pst = con.prepareStatement(delFootSql);
	        for (Integer businessId : businessIds) {
	            pst.setInt(1, businessId);
	            pst.addBatch();
	        }
	        pst.executeBatch();

	        pst = con.prepareStatement(delBusinessSql);
	        for (Integer businessId : businessIds) {
	            pst.setInt(1, businessId);
	            pst.addBatch();
	        }
	        int[] deleteCounts = pst.executeBatch();
	        for (int deleteCount : deleteCounts) {
	            if (deleteCount > 0) { 
	                result++;
	            }
	        }

	        pst = con.prepareStatement(updateBusinessSql);
	        for (Integer businessId : businessIds) {
	            pst.setInt(1, businessId);
	            pst.addBatch();
	        }
	        pst.executeBatch();

	        con.commit();

	    } catch (SQLException e) {
	        result = 0;
	        try {
	            con.rollback();
	        } catch (SQLException e1) {
	            e1.printStackTrace();
	        }
	        e.printStackTrace();
	    } finally {
	        DBUtil.close(null, pst, con);
	    }
	    return result;
	}
	
	
	@Override
	public Business getBusinessByIdByPass(Integer businessId,String password) {
		Business business = null;
	    String sql ="select * from business where businessId=? and password=? ";	    	
	    	try {
	    		con = DBUtil.getConnection();
				pst = con.prepareStatement(sql.toString());
				pst.setInt(1,businessId);
				pst.setString(2,password);
		    	rs = pst.executeQuery();
		    	while(rs.next()) {
		    	    business = new Business();
		    	    business.setBusinessId(rs.getInt("businessId"));
		    	    business.setPassword(rs.getString("password"));
		    	    business.setBusinessName(rs.getString("businessName"));
		    	    business.setBusinessAddress(rs.getString("businessAddress"));
		    	    business.setBusinessExplain(rs.getString("businessExplain"));
		    	    business.setStarPrice(rs.getDouble("starPrice"));
		    	    business.setDeliveryPrice(rs.getDouble("deliveryPrice"));
		    	    business.setDeliveryDistance(rs.getString("deliveryDistance"));
		    	    
		    	}
	    	}catch (SQLException e) {
				e.printStackTrace();
			}finally {
				DBUtil.close(rs, pst, con);
			}
	    	return business;
	}
	
	
	
	@Override
	public Business getBusinessById(Integer businessId) {
		Business business = null;
	    String sql ="select * from business where businessId=?";	    	
	    	try {
	    		con = DBUtil.getConnection();
				pst = con.prepareStatement(sql.toString());
				pst.setInt(1,businessId);
		    	rs = pst.executeQuery();
		    	while(rs.next()) {
		    	    business = new Business();
		    	    business.setBusinessId(rs.getInt("businessId"));
		    	    business.setPassword(rs.getString("password"));
		    	    business.setBusinessName(rs.getString("businessName"));
		    	    business.setBusinessAddress(rs.getString("businessAddress"));
		    	    business.setBusinessExplain(rs.getString("businessExplain"));
		    	    business.setStarPrice(rs.getDouble("starPrice"));
		    	    business.setDeliveryPrice(rs.getDouble("deliveryPrice"));
		    	    business.setDeliveryDistance(rs.getString("deliveryDistance"));
		    	    
		    	}
	    	}catch (SQLException e) {
				e.printStackTrace();
			}finally {
				DBUtil.close(rs, pst, con);
			}
	    	return business;
	}
	
	@Override
	public int updateBusiness(Business business) {
		int result = 0;
	    String sql ="update business set businessName=?,businessAddress=?,businessExplain=?,starPrice=?,deliveryPrice=?,deliveryDistance=? where businessId=?";
	    	try {
	    		con = DBUtil.getConnection();
                pst = con.prepareStatement(sql);
                pst.setString(1, business.getBusinessName());
                pst.setString(2, business.getBusinessAddress());
	    		pst.setString(3, business.getBusinessExplain());
	    		pst.setDouble(4, business.getStarPrice());
	    		pst.setDouble(5, business.getDeliveryPrice());
	    		pst.setString(6,business.getDeliveryDistance());
	    		pst.setInt(7, business.getBusinessId());
	    		result = pst.executeUpdate();
	    	}catch (SQLException e) {
				e.printStackTrace();
			}finally {
				DBUtil.close(null, pst, con);
			}
	    	return result;
		}
	
	@Override
	public int updateBusinessPassword(Integer businessId,String password) {
		int result = 0;
	    String sql ="update business set password=? where businessId=?";
	    	try {
	    		con = DBUtil.getConnection();
                pst = con.prepareStatement(sql);   
                pst.setString(1, password);	 
                pst.setInt(2, businessId);
	    		result = pst.executeUpdate();
	    	}catch (SQLException e) {
				e.printStackTrace();
			}finally {
				DBUtil.close(null, pst, con);
			}
	    	return result;
	}
	
	@Override
	public int countBusiness() {
	    int count = 0;
	    String sql = "SELECT COUNT(*) FROM business";

	    try {
	        con = DBUtil.getConnection();
	        pst = con.prepareStatement(sql);
	        rs = pst.executeQuery();
	        if (rs.next()) {
	            count = rs.getInt(1);
	        }
	    } catch (SQLException e) {
	        e.printStackTrace();
	    } finally {
	        DBUtil.close(rs, pst, con);
	    }

	    return count;
	}
	
}

